import util.excelUtils as U
import pymysql

headers = ['digest', '名字', "省", "市", "区", '审计机关'  '信用号', '注册号']
info = {"Sheet": [headers]}

db = pymysql.connect(host="rm-bp152ng910khxljk85o.mysql.rds.aliyuncs.com",
                     port=3306,
                     user="dev",
                     password="3NG1LRYKCVKa8dQZ+HkpcQ",
                     database="qierge_32"
                     )
# sql="select * from py_company_base where districtId='320507' and regInstitute is not null and regInstitute!='' and   regInstitute  not  like '%相城%'"
# sql="select * from py_company_base where districtId='320507' and regInstitute is not null and regInstitute!='' and   (regInstitute  not  like '%相城%' and regInstitute not  like '%苏州%' )"
# sql="select * from base where regStatus in(1)   and (phone is not null and phone != '' and phone!='-') and `districtId` = '320507' and entType = 9500 and digest not in (select digest from base2 where regStatus in(1)  and (phone is not null and phone != '' and phone!='-') and `districtId` = '320507' and entType = 9500)"
sql = "select * from base2 where regStatus in(1)  and (phone is not null and phone != '' and phone!='-') and `districtId` = '320507' and entType = 9500 and digest not in (select digest from base where regStatus in(1)  and errorState!=2 and (phone is not null and phone != '' and phone!='-') and `districtId` = '320507' and entType = 9500)"
cursor = db.cursor()
cursor.execute(sql)
dataList = cursor.fetchall()

for data in dataList:
    print(data)
    d = [data[2], data[3], data[17], data[19], data[21], data[38], data[13], data[14]]
    info["Sheet"].append(d)
U.batch_excel(info, "旧版比新版多的.xlsx")
